package top.abilities.util.utils.Excel;


import com.google.common.io.Files;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.openxml4j.opc.internal.ZipHelper;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.web.util.HtmlUtils;
import top.abilities.util.domain.LabelExportEntity;
import top.abilities.util.param.AliOssParamVO;
import top.abilities.util.util.LocalDateUtil;
import top.abilities.util.util.OssClientUtil;
import top.abilities.util.util.StringUtils;

import javax.annotation.PostConstruct;
import java.io.*;
import java.lang.reflect.Method;
import java.util.*;
import java.util.zip.ZipEntry;
import java.util.zip.ZipFile;
import java.util.zip.ZipOutputStream;

import static org.springframework.web.util.HtmlUtils.htmlEscape;

/**
 * @Auhtor: ciro
 * @Description
 * @Date: 2017/12/21 14:45
 * @Modified By: dong
 * @Date: 2019/11/4
 */
@Component
public class ExcelGigGridEdit {
    private static Logger logger = LoggerFactory.getLogger(ExcelGigGridEdit.class);
    private static final String XML_ENCODING = "UTF-8";

    @Autowired
    AliOssParamVO aliOssParamVO;
    private static AliOssParamVO staticAliOssParamVO;

    @PostConstruct
    public void init(){
        staticAliOssParamVO=aliOssParamVO;
    }

    public static<T> Map creatExcel(String sheetName, String excelName, String[] headerNames, String[] fieldNames, List<T> list)throws Exception{
        String ossUrl="";
        Long Ibegin,Iend;
        // Step 1. Create a template file. Setup sheets and workbook-level objects such as
        // cell styles, number formats, etc.
        XSSFWorkbook wb = new XSSFWorkbook();
        Ibegin=System.currentTimeMillis();
        XSSFSheet sheet = wb.createSheet(sheetName);
        Map<String, XSSFCellStyle> styles = createStyles(wb);
        //name of the zip entry holding sheet data, e.g. /xl/worksheets/sheet1.xml
        String sheetRef = sheet.getPackagePart().getPartName().getName();
        //save the template
        File tmpFile = new File(excelName+"_template.xlsx");
        try (FileOutputStream os = new FileOutputStream(tmpFile)) {
            wb.write(os);
        }
        //Step 2. Generate XML file.
        File tmp = File.createTempFile("sheet", ".xml");
        try (Writer fw = new OutputStreamWriter(new FileOutputStream(tmp), XML_ENCODING)) {
            generate(fw, styles,headerNames,fieldNames,list);
        }
        //Step 3. Substitute the template entry with the generated data
        File realFile=new File(excelName+".xlsx");
        try (FileOutputStream out = new FileOutputStream(realFile)) {
            substitute(new File(excelName+"_template.xlsx"), tmp, sheetRef.substring(1), out);
        }
        tmp.delete();
        tmpFile.delete();
//        String result=OssClientUtil.uploadObject2OSS(realFile,staticAliOssParamVO.getBucketName(),staticAliOssParamVO.getKeyExcelPrefix());
        String result= OssClientUtil.uploadObject2OSS(realFile,staticAliOssParamVO.getBucketFescoGapPriavte(),staticAliOssParamVO.getKeyExcelPrefix());
        realFile.delete();
        StringBuffer buffer=new StringBuffer();
        buffer.append("https://");
//        buffer.append(staticAliOssParamVO.getBucketName());
        buffer.append(staticAliOssParamVO.getBucketFescoGapPriavte());
        buffer.append(".");
        buffer.append(staticAliOssParamVO.getEndpoint());
        buffer.append("/");
        buffer.append(staticAliOssParamVO.getKeyExcelPrefix());
        buffer.append(realFile.getName());
        ossUrl=buffer.toString();
        Iend=System.currentTimeMillis();
        logger.info("耗时{}秒",(Iend-Ibegin)/1000);
        Map map=new HashMap();
        map.put("url",ossUrl);
        map.put("key",result);
        return map;
    }

    public static <T> Map creatExcel(String excelName, LabelExportEntity entity) throws Exception {
        String ossUrl = "";
        Long Ibegin, Iend;
        // Step 1. Create a template file. Setup sheets and workbook-level objects such as
        // cell styles, number formats, etc.
        XSSFWorkbook wb = new XSSFWorkbook();
        Ibegin = System.currentTimeMillis();
        XSSFSheet labelSheet = wb.createSheet(entity.getLabelSheet());
        XSSFSheet aListSheet = wb.createSheet(entity.getaListSheet());
        Map<String, XSSFCellStyle> styles = createStyles(wb);
        Map<String, XSSFCellStyle> styles2 = createStyles(wb);
        //name of the zip entry holding sheet data, e.g. /xl/worksheets/sheet1.xml
        String sheetRefOne = labelSheet.getPackagePart().getPartName().getName();
        String sheetRefTwo = aListSheet.getPackagePart().getPartName().getName();
        //save the template
        File tmpFile = new File(excelName + "_template.xlsx");
        try (FileOutputStream os = new FileOutputStream(tmpFile)) {
            wb.write(os);
        }
        wb.close();
        File realFile = new File(excelName+".xlsx");
        Files.copy(tmpFile,realFile);
        //Step 2. Generate XML file.
        File tmpOne = File.createTempFile("sheet1", ".xml");
        try (Writer fw = new OutputStreamWriter(new FileOutputStream(tmpOne), XML_ENCODING)) {
            generate(fw, styles, entity.getLabelFirsts(), entity.getTheLabelValues(), entity.getLabelData());
        }
        //More than one sheet
        File tmpTwo = File.createTempFile("sheet2", ".xml");
        try (Writer fw = new OutputStreamWriter(new FileOutputStream(tmpTwo), XML_ENCODING)) {
            generate(fw, styles2, entity.getListHeads(), entity.getTheListOfValues(), entity.getTableData());
        }
        //Step 3. Substitute the template entry with the generated data
        try (FileOutputStream out = new FileOutputStream(realFile)) {
            substitute(tmpFile,tmpOne, sheetRefOne.substring(1), tmpTwo, sheetRefTwo.substring(1), out);
        }
        tmpOne.delete();
        tmpTwo.delete();
        tmpFile.delete();
//        String result = OssClientUtil.uploadObject2OSS(realFile, staticAliOssParamVO.getBucketName(), staticAliOssParamVO.getKeyExcelPrefix());
        String result = OssClientUtil.uploadObject2OSS(realFile, staticAliOssParamVO.getBucketFescoGapPriavte(), staticAliOssParamVO.getKeyExcelPrefix());
        realFile.delete();
        StringBuffer buffer = new StringBuffer();
        buffer.append("https://");
//        buffer.append(staticAliOssParamVO.getBucketName());
        buffer.append(staticAliOssParamVO.getBucketFescoGapPriavte());
        buffer.append(".");
        buffer.append(staticAliOssParamVO.getEndpoint());
        buffer.append("/");
        buffer.append(staticAliOssParamVO.getKeyExcelPrefix());
        buffer.append(realFile.getName());
        ossUrl = buffer.toString();
        Iend = System.currentTimeMillis();
        logger.info("耗时{}秒", (Iend - Ibegin) / 1000);
        Map map = new HashMap();
        map.put("url", ossUrl);
        map.put("key", result);
        return map;
    }

    /**
     * Create a library of cell styles.
     */
    private static Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb){
        Map<String, XSSFCellStyle> styles = new HashMap<>();
        XSSFDataFormat fmt = wb.createDataFormat();

        XSSFCellStyle style1 = wb.createCellStyle();
        style1.setAlignment(HorizontalAlignment.RIGHT);
        style1.setDataFormat(fmt.getFormat("0.0%"));
        styles.put("percent", style1);

        XSSFCellStyle style2 = wb.createCellStyle();
        style2.setAlignment(HorizontalAlignment.CENTER);
        style2.setDataFormat(fmt.getFormat("0.0X"));
        styles.put("coeff", style2);

        XSSFCellStyle style3 = wb.createCellStyle();
        style3.setAlignment(HorizontalAlignment.RIGHT);
        style3.setDataFormat(fmt.getFormat("$#,##0.00"));
        styles.put("currency", style3);

        XSSFCellStyle style4 = wb.createCellStyle();
        style4.setAlignment(HorizontalAlignment.RIGHT);
        style4.setDataFormat(fmt.getFormat("mmm dd"));
        styles.put("date", style4);

        XSSFCellStyle style5 = wb.createCellStyle();
        XSSFFont headerFont = wb.createFont();
        headerFont.setBold(true);
        style5.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style5.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style5.setFont(headerFont);
        styles.put("header", style5);

        return styles;
    }

    private static<T> void generate(Writer out, Map<String, XSSFCellStyle> styles, String[] headerNames, String[] fieldNames, List<T> list) throws Exception {

        SpreadsheetWriter sw = new SpreadsheetWriter(out);
        sw.beginSheet();

        //insert header row
        sw.insertRow(0);
        int styleIndex = styles.get("header").getIndex();
        for(int i=0;i<headerNames.length;i++){
            sw.createCell(i, headerNames[i], styleIndex);
        }
        sw.endRow();

        //write data rows
        for (int rownum = 1; rownum <= list.size(); rownum++) {
            sw.insertRow(rownum);
            T t=list.get(rownum-1);
         /*   sw.createCell(0, "Hello, " + rownum + "!");
            sw.createCell(1, (double)rnd.nextInt(100)/100, styles.get("percent").getIndex());
            sw.createCell(2, (double)rnd.nextInt(10)/10, styles.get("coeff").getIndex());
            sw.createCell(3, rnd.nextInt(10000), styles.get("currency").getIndex());
            sw.createCell(4, calendar, styles.get("date").getIndex());*/
            for (int j=0;j<fieldNames.length;j++) {
                Method[] methods=t.getClass().getMethods();
                for (Method method : methods) {
                    if(t.getClass().getTypeName().contains("Map")){
                        if(("get").equalsIgnoreCase(method.getName())){
                            Object value = method.invoke(t, new Object[]{fieldNames[j]});
                            if(value!=null&&value.getClass().getTypeName().contains("Date")){
                                sw.createCell(j, LocalDateUtil.dateToString((Date)value,"yyyy-MM-dd HH:mm:ss"));
                            }else{
                                sw.createCell(j, StringUtils.uniCodeFromExcel(String.valueOf(value)));
                            }

                        }
                    }else{
                        if(("get"+fieldNames[j]).equalsIgnoreCase(method.getName())){
                            Object value = method.invoke(t, new Object[]{});
                            if(value!=null&&value.getClass().getTypeName().contains("Date")){
                                sw.createCell(j, LocalDateUtil.dateToString((Date)value,"yyyy-MM-dd HH:mm:ss"));
                            }else{
                                sw.createCell(j, StringUtils.uniCodeFromExcel(String.valueOf(value)));
                            }
                            /*sw.createCell(j, String.valueOf(value));*/
                        }
                    }

                }

            }

            sw.endRow();

        }
        sw.endSheet();
    }
    /**
     *
     * @param zipfile the template file
     * @param tmpfile the XML file with the sheet data
     * @param entry the name of the sheet entry to substitute, e.g. xl/worksheets/sheet1.xml
     * @param out the stream to write the result to
     */
    private static void substitute(File zipfile, File tmpfile, String entry, OutputStream out) throws IOException {
        try (ZipFile zip = ZipHelper.openZipFile(zipfile)) {
            try (ZipOutputStream zos = new ZipOutputStream(out)) {
                Enumeration<? extends ZipEntry> en = zip.entries();
                while (en.hasMoreElements()) {
                    ZipEntry ze = en.nextElement();
                    if (!ze.getName().equals(entry)) {
                        zos.putNextEntry(new ZipEntry(ze.getName()));
                        try (InputStream is = zip.getInputStream(ze)) {
                            copyStream(is, zos);
                        }
                    }
                }
                zos.putNextEntry(new ZipEntry(entry));
                try (InputStream is = new FileInputStream(tmpfile)) {
                    copyStream(is, zos);
                }
            }
        }
    }

    /**
     * @param zipfile the template file
     * @param entrys  the name of the sheet entry to substitute, e.g. xl/worksheets/sheet1.xml
     * @param out     the stream to write the result to
     */
    private static void substitute(File realFile,File zipfile, String entrys,File tmpTwo,String sheetRef2, OutputStream out) throws IOException {
        try (ZipFile zip = ZipHelper.openZipFile(realFile)) {
            try (ZipOutputStream zos = new ZipOutputStream(out)) {
                Enumeration<? extends ZipEntry> en = zip.entries();
                while (en.hasMoreElements()) {
                    ZipEntry ze = en.nextElement();
                    if (!ze.getName().equals(entrys) && ! ze.getName().equals(sheetRef2)) {
                        zos.putNextEntry(new ZipEntry(ze.getName()));
                        try (InputStream is = zip.getInputStream(ze)) {
                            copyStream(is, zos);
                        }
                    }
                }
                zos.putNextEntry(new ZipEntry(entrys));
                try (InputStream is = new FileInputStream(zipfile)) {
                    copyStream(is, zos);
                }
                zos.putNextEntry(new ZipEntry(sheetRef2));
                try (InputStream is = new FileInputStream(tmpTwo)) {
                    copyStream(is, zos);
                }
            }
        }

    }


    private static void copyStream(InputStream in, OutputStream out) throws IOException {
        byte[] chunk = new byte[1024];
        int count;
        while ((count = in.read(chunk)) >=0 ) {
            out.write(chunk,0,count);
        }
    }

    /**
     * Writes spreadsheet data in a Writer.
     * (YK: in future it may evolve in a full-featured API for streaming data in Excel)
     */
    public static class SpreadsheetWriter {
        private final Writer _out;
        private int _rownum;

        public SpreadsheetWriter(Writer out){
            _out = out;
        }

        public void beginSheet() throws IOException {
            _out.write("<?xml version=\"1.0\" encoding=\""+XML_ENCODING+"\"?>" +
                    "<worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">" );
            _out.write("<sheetData>\n");
        }

        public void endSheet() throws IOException {
            _out.write("</sheetData>");
            _out.write("</worksheet>");
        }

        /**
         * Insert a new row
         *
         * @param rownum 0-based row number
         */
        public void insertRow(int rownum) throws IOException {
            _out.write("<row r=\""+(rownum+1)+"\">\n");
            this._rownum = rownum;
        }

        /**
         * Insert row end marker
         */
        public void endRow() throws IOException {
            _out.write("</row>\n");
        }

        public void createCell(int columnIndex, String value, int styleIndex) throws IOException {
            String ref = new CellReference(_rownum, columnIndex).formatAsString();
            _out.write("<c r=\""+ref+"\" t=\"inlineStr\"");
            if(styleIndex != -1) _out.write(" s=\""+styleIndex+"\"");
            _out.write(">");
            _out.write("<is><t>"+value+"</t></is>");
            _out.write("</c>");
        }

        public void createCell(int columnIndex, String value) throws IOException {
            createCell(columnIndex, value, -1);
        }

        public void createCell(int columnIndex, double value, int styleIndex) throws IOException {
            String ref = new CellReference(_rownum, columnIndex).formatAsString();
            _out.write("<c r=\""+ref+"\" t=\"n\"");
            if(styleIndex != -1) _out.write(" s=\""+styleIndex+"\"");
            _out.write(">");
            _out.write("<v>"+value+"</v>");
            _out.write("</c>");
        }

        public void createCell(int columnIndex, double value) throws IOException {
            createCell(columnIndex, value, -1);
        }

        public void createCell(int columnIndex, Calendar value, int styleIndex) throws IOException {
            createCell(columnIndex, DateUtil.getExcelDate(value, false), styleIndex);
        }
    }
}
